package hc.um.service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import hc.service.AService;
import hc.tool.UtilityServiec;
import hc.um.Drug;
import hc.um.Employee;
import hc.um.Hospital;
import hc.um.Role;
import hc.um.Schedule;
import hc.um.ScheduleDetail;
import hc.um.TimeSlice;

import java.text.DateFormat;
import java.text.SimpleDateFormat;

public class UserService extends AService {

	public Employee checkUser(String userID, String pwd) {
		Employee employee = null;
		Statement sment = null;
		ResultSet rs = null;
		DateFormat dateFormat = new SimpleDateFormat("dd-mmm-yyyy");
		try {
			sment = connection.createStatement();
			String sql = "select * from Employee em where em.EmployeeID = '"
					+ userID + "' and em.password='" + pwd + "'";
			System.out.println(sql);
			rs = sment.executeQuery(sql);
			if (rs != null && rs.next()) {
				employee = new Employee();
				employee.setID(rs.getString("EmployeeID"));
				employee.setLastName(rs.getString("lastName"));
				employee.setFirstName(rs.getString("firstName"));
				employee.setBirthday(dateFormat.format(rs.getDate("birthday")));
				employee.setSex(rs.getString("Sex"));
				employee.setRoleID(rs.getInt("RoleID"));
			}
			rs.close();
			sment.close();
		} catch (SQLException e) {
			e.printStackTrace();
			employee = null;
		} finally {
			try {
				rs.close();
				sment.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return employee;
	}

	public int addUser(Employee wemployee) throws SQLException {
		PreparedStatement preparedStatement = null;

		int number = 0;

		String query = "INSERT INTO employee VALUES (?, ?, ?, ?, to_date(?,'dd-mon-yy'), ?, ?, ?, ?, ?, ?)";
		System.out.println(query);
		try {
			preparedStatement = connection.prepareStatement(query);

			System.out.println("id=" + wemployee.getID());
			preparedStatement.setString(1, wemployee.getID());
			System.out.println("pwd=" + wemployee.getPwd());
			preparedStatement.setString(2, wemployee.getPwd());
			System.out.println("lname=" + wemployee.getLastName());
			preparedStatement.setString(3, wemployee.getLastName());
			System.out.println("fname=" + wemployee.getFirstName());
			preparedStatement.setString(4, wemployee.getFirstName());
			System.out.println("birthday=" + wemployee.getBirthday());
			preparedStatement.setString(5, wemployee.getBirthday());
			System.out.println("sex=" + wemployee.getSex());
			preparedStatement.setString(6, wemployee.getSex());
			System.out.println("phone=" + wemployee.getPhone());
			preparedStatement.setString(7, wemployee.getPhone());
			System.out.println("email=" + wemployee.getEmail());
			preparedStatement.setString(8, wemployee.getEmail());
			System.out.println("address=" + wemployee.getAddress());
			preparedStatement.setString(9, wemployee.getAddress());
			System.out.println("hospitalid=" + wemployee.getHospitalid());
			preparedStatement.setInt(10, wemployee.getHospitalid());
			System.out.println("roleid=" + wemployee.getRoleID());
			preparedStatement.setInt(11, wemployee.getRoleID());

			number = preparedStatement.executeUpdate();

			return number;
		}

		catch (SQLException e) {
			try {
				connection.rollback();
			} catch (Exception ee) {
				System.out.println(ee.getMessage());
			}
			System.out.println(e.getMessage());

			e.printStackTrace();
		}

		finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}

		return number;
	}

	public int updateUser(Employee wemployee) throws SQLException {
		PreparedStatement preparedStatement = null;

		int number = 0;

		String query = "UPDATE employee SET lastName=?, firstName=?, birthday=to_date(?,'dd-mon-yy'), "
				+ "Sex=?, phone=?, email=?, address=?, hospitalid=?, RoleID=? WHERE EmployeeID=?";
		System.out.println(query);
		try {
			preparedStatement = connection.prepareStatement(query);

			System.out.println("lname=" + wemployee.getLastName());
			preparedStatement.setString(1, wemployee.getLastName());
			System.out.println("fname=" + wemployee.getFirstName());
			preparedStatement.setString(2, wemployee.getFirstName());
			System.out.println("birthday=" + wemployee.getBirthday());
			preparedStatement.setString(3, wemployee.getBirthday());
			System.out.println("sex=" + wemployee.getSex());
			preparedStatement.setString(4, wemployee.getSex());
			System.out.println("phone=" + wemployee.getPhone());
			preparedStatement.setString(5, wemployee.getPhone());
			System.out.println("email=" + wemployee.getEmail());
			preparedStatement.setString(6, wemployee.getEmail());
			System.out.println("address=" + wemployee.getAddress());
			preparedStatement.setString(7, wemployee.getAddress());
			System.out.println("hospitalid=" + wemployee.getHospitalid());
			preparedStatement.setInt(8, wemployee.getHospitalid());
			System.out.println("roleid=" + wemployee.getRoleID());
			preparedStatement.setInt(9, wemployee.getRoleID());
			System.out.println("id=" + wemployee.getID());
			preparedStatement.setString(10, wemployee.getID());

			number = preparedStatement.executeUpdate();

			return number;
		}

		catch (SQLException e) {
			try {
				connection.rollback();
			} catch (Exception ee) {
				System.out.println(ee.getMessage());
			}
			System.out.println(e.getMessage());

			e.printStackTrace();
		}

		finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}

		return number;
	}

	public int deleteUser(String wuserid) throws SQLException {
		PreparedStatement preparedStatement = null;

		int number = 0;

		String query = "DELETE FROM employee WHERE EmployeeID=?";
		System.out.println(query);
		try {
			preparedStatement = connection.prepareStatement(query);

			System.out.println("id=" + wuserid);
			preparedStatement.setString(1, wuserid);

			number = preparedStatement.executeUpdate();

			return number;
		}

		catch (SQLException e) {
			try {
				connection.rollback();
			} catch (Exception ee) {
				System.out.println(ee.getMessage());
			}
			System.out.println(e.getMessage());

			e.printStackTrace();
		}

		finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}

		return number;
	}

	public Employee getEmployeeById(String id) {
		Employee employee = null;
		Role role = null;
		Statement sment = null;
		ResultSet rs = null;
		DateFormat dateFormat = new SimpleDateFormat("dd-mmm-yyyy");
		try {
			sment = connection.createStatement();
			String sql = "select em.*, rl.RoleName  from Employee em, Role rl where em.EmployeeID = '"
					+ id + "' and em.RoleID = rl.RoleID";
			System.out.println(sql);
			rs = sment.executeQuery(sql);

			if (rs != null && rs.next()) {
				employee = new Employee();
				employee.setID(rs.getString("EmployeeID"));
				employee.setLastName(rs.getString("lastName"));
				employee.setFirstName(rs.getString("firstName"));
				employee.setBirthday(dateFormat.format(rs.getDate("birthday")));
				employee.setSex(rs.getString("Sex"));
				employee.setPhone(rs.getString("phone"));
				employee.setEmail(rs.getString("email"));
				employee.setAddress(rs.getString("address"));
				employee.setHospitalid(rs.getInt("hospitalid"));
				employee.setRoleID(rs.getInt("RoleID"));

				role = new Role();
				role.setID(rs.getInt("RoleID"));
				role.setName(rs.getString("RoleName"));
				employee.setRole(role);
			}
			rs.close();
			sment.close();
		} catch (SQLException e) {
			e.printStackTrace();
			employee = null;
		} finally {
			try {
				rs.close();
				sment.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return employee;
	}

	public String getHospitalRecords() {
		Hospital hospital = null;
		String hospitalList = "";

		Statement sment = null;
		ResultSet rs = null;

		try {
			sment = connection.createStatement();
			String sql = "SELECT * FROM Hospital ORDER BY HospitalID";
			System.out.println(sql);

			hospital = new Hospital();

			rs = sment.executeQuery(sql);

			while (rs.next()) {

				hospital.setID(rs.getInt("HospitalID"));
				hospital.setName(rs.getString("HospitalName"));
				hospital.setAddress(rs.getString("address"));
				hospital.setPostcode(rs.getString("zcode"));
				hospital.setPhone(rs.getString("phone"));
				hospital.setFax(rs.getString("fax"));
				hospital.setEmail(rs.getString("email"));

				hospitalList += creatXmlTextHospitals(hospital);
			}

			hospitalList = "<hospitalquery>" + hospitalList
					+ "</hospitalquery>";

			rs.close();
			sment.close();

		}

		catch (SQLException e) {
			e.printStackTrace();
			hospitalList = "";
		}

		finally {
			try {
				rs.close();
				sment.close();
			}

			catch (SQLException e) {
				e.printStackTrace();
			}
		}

		// System.out.println("hospitalList="+hospitalList);

		return hospitalList;
	}

	public String creatXmlTextHospitals(Hospital whospital) {
		String xmltext = "";

		xmltext = "<RD>" + "<id>" + whospital.getID() + "</id>" + "<name>"
				+ whospital.getName() + "</name>" + "<address>"
				+ whospital.getAddress() + "</address>" + "<zcode>"
				+ whospital.getPostcode() + "</zcode>" + "<phone>"
				+ whospital.getPhone() + "</phone>" + "<fax>"
				+ whospital.getFax() + "</fax>" + "<email>"
				+ whospital.getEmail() + "</email>" + "</RD>";
		System.out.println("xmltext=" + xmltext);

		return xmltext;
	}

	public String getRoleRecords() {
		Role role = null;
		String roleList = "";

		Statement sment = null;
		ResultSet rs = null;

		try {
			sment = connection.createStatement();
			String sql = "SELECT * FROM Role ORDER BY RoleID";
			System.out.println(sql);

			role = new Role();

			rs = sment.executeQuery(sql);

			while (rs.next()) {

				role.setID(rs.getInt("RoleID"));
				role.setName(rs.getString("RoleName"));

				roleList += creatXmlTextRoles(role);
			}

			roleList = "<rolequery>" + roleList + "</rolequery>";

			rs.close();
			sment.close();

		}

		catch (SQLException e) {
			e.printStackTrace();
			roleList = "";
		}

		finally {
			try {
				rs.close();
				sment.close();
			}

			catch (SQLException e) {
				e.printStackTrace();
			}
		}

		// System.out.println("roleList="+roleList);

		return roleList;
	}

	public String creatXmlTextRoles(Role wrole) {
		String xmltext = "";

		xmltext = "<RD>" + "<id>" + wrole.getID() + "</id>" + "<name>"
				+ wrole.getName() + "</name>" + "</RD>";
		System.out.println("xmltext=" + xmltext);

		return xmltext;
	}

	public int getUserIdSequence(int id) {
		Statement sment = null;
		ResultSet rs = null;
		int sequence = 0;

		try {
			sment = connection.createStatement();
			String sql = "select sequence from usersequence where roleid = '"
					+ id + "'";
			System.out.println(sql);
			rs = sment.executeQuery(sql);

			if (rs != null && rs.next()) {
				sequence = rs.getInt("sequence");
			}
			rs.close();
			sment.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				sment.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return sequence;
	}

	public int updateUserIdSequence(int id, int seq) {
		int number = 0;
		PreparedStatement preparedStatement = null;

		String query = "UPDATE usersequence SET sequence=? WHERE roleid = ?";
		System.out.println(query);
		try {
			preparedStatement = connection.prepareStatement(query);

			preparedStatement.setInt(1, seq);
			preparedStatement.setInt(2, id);

			number = preparedStatement.executeUpdate();

			return number;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return number;
	}

	public int addHospital(Hospital whospital) throws SQLException {
		PreparedStatement preparedStatement = null;

		int number = 0;

		String query = "INSERT INTO hospital VALUES ("
				+ UtilityServiec.getHostpitalSeqVal() + ", ?, ?, ?, ?, ?, ?)";

		System.out.println(query);
		try {
			preparedStatement = connection.prepareStatement(query);

			System.out.println("name=" + whospital.getName());
			preparedStatement.setString(1, whospital.getName());
			System.out.println("address=" + whospital.getAddress());
			preparedStatement.setString(2, whospital.getAddress());

			System.out.println("zcode=" + whospital.getPostcode());
			preparedStatement.setString(3, whospital.getPostcode());
			System.out.println("phone=" + whospital.getPhone());
			preparedStatement.setString(4, whospital.getPhone());
			System.out.println("fax=" + whospital.getFax());
			preparedStatement.setString(5, whospital.getFax());
			System.out.println("email=" + whospital.getEmail());
			preparedStatement.setString(6, whospital.getEmail());

			number = preparedStatement.executeUpdate();

			return number;
		}

		catch (SQLException e) {
			try {
				connection.rollback();
			} catch (Exception ee) {
				System.out.println(ee.getMessage());
			}
			System.out.println(e.getMessage());

			e.printStackTrace();
		}

		finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}

		return number;
	}
	
	public int updateHospital(Hospital whospital) throws SQLException {
		PreparedStatement preparedStatement = null;

		int number = 0;

		String query = "UPDATE hospital SET HospitalName=?, address=?, zcode=?, "
				+ "phone=?, fax=?, email=? WHERE HospitalID=?";
		System.out.println(query);
		try {
			preparedStatement = connection.prepareStatement(query);

			System.out.println("HospitalName=" + whospital.getName());
			preparedStatement.setString(1, whospital.getName());
			System.out.println("address=" + whospital.getAddress());
			preparedStatement.setString(2, whospital.getAddress());
			System.out.println("zcode=" + whospital.getPostcode());
			preparedStatement.setString(3, whospital.getPostcode());
			System.out.println("phone=" + whospital.getPhone());
			preparedStatement.setString(4, whospital.getPhone());
			System.out.println("fax=" + whospital.getFax());
			preparedStatement.setString(5, whospital.getFax());
			System.out.println("email=" + whospital.getEmail());
			preparedStatement.setString(6, whospital.getEmail());
			System.out.println("HospitalID=" + whospital.getID());
			preparedStatement.setInt(7, whospital.getID());

			number = preparedStatement.executeUpdate();

			return number;
		}

		catch (SQLException e) {
			try {
				connection.rollback();
			} catch (Exception ee) {
				System.out.println(ee.getMessage());
			}
			System.out.println(e.getMessage());

			e.printStackTrace();
		}

		finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}

		return number;
	}
	
	public int deleteHospital(int whospitalid) throws SQLException {
		PreparedStatement preparedStatement = null;

		int number = 0;

		String query = "DELETE FROM Hospital WHERE HospitalID=?";
		System.out.println(query);
		try {
			preparedStatement = connection.prepareStatement(query);

			System.out.println("HospitalID=" + whospitalid);
			preparedStatement.setInt(1, whospitalid);

			number = preparedStatement.executeUpdate();

			return number;
		}

		catch (SQLException e) {
			try {
				connection.rollback();
			} catch (Exception ee) {
				System.out.println(ee.getMessage());
			}
			System.out.println(e.getMessage());

			e.printStackTrace();
		}

		finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}

		return number;
	}
	
	public String getDrugRecords() {
		Drug drugs = null;
		String drugsList = "";

		Statement sment = null;
		ResultSet rs = null;

		try {
			sment = connection.createStatement();
			String sql = "SELECT * FROM Drug ORDER BY drugID";
			System.out.println(sql);

			drugs = new Drug();

			rs = sment.executeQuery(sql);

			while (rs.next()) {

				drugs.setID(rs.getInt("drugID"));
				drugs.setName(rs.getString("name"));
				drugs.setSpecification(rs.getString("comments"));

				drugsList += creatXmlTextDrugs(drugs);
			}

			drugsList = "<drugquery>" + drugsList
					+ "</drugquery>";

			rs.close();
			sment.close();

		}

		catch (SQLException e) {
			e.printStackTrace();
			drugsList = "";
		}

		finally {
			try {
				rs.close();
				sment.close();
			}

			catch (SQLException e) {
				e.printStackTrace();
			}
		}

		// System.out.println("hospitalList="+hospitalList);

		return drugsList;
	}

	public String creatXmlTextDrugs(Drug wdrugs) {
		String xmltext = "";

		xmltext = "<RD>" + "<id>" + wdrugs.getID() + "</id>" + "<name>"
				+ wdrugs.getName() + "</name>" + "<specification>"
				+ wdrugs.getSpecification() + "</specification>"  + "</RD>";
		System.out.println("xmltext=" + xmltext);

		return xmltext;
	}
	
	public int addDrug(Drug wdrug) throws SQLException {
		PreparedStatement preparedStatement = null;

		int number = 0;

		String query = "INSERT INTO drug VALUES (?, ?, ?)";
		System.out.println(query);
		try {
			preparedStatement = connection.prepareStatement(query);

			System.out.println("id=" + wdrug.getID());
			preparedStatement.setInt(1, wdrug.getID());
			System.out.println("name=" + wdrug.getName());
			preparedStatement.setString(2, wdrug.getName());
			System.out.println("specification=" + wdrug.getSpecification());
			preparedStatement.setString(3, wdrug.getSpecification());		

			number = preparedStatement.executeUpdate();

			return number;
		}

		catch (SQLException e) {
			try {
				connection.rollback();
			} catch (Exception ee) {
				System.out.println(ee.getMessage());
			}
			System.out.println(e.getMessage());

			e.printStackTrace();
		}

		finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}

		return number;
	}	
	
	public int updateDrug(Drug wdrug) throws SQLException {
		PreparedStatement preparedStatement = null;

		int number = 0;

		String query = "UPDATE drug SET name=?, comments=? WHERE drugID=?";
		System.out.println(query);
		try {
			preparedStatement = connection.prepareStatement(query);

			System.out.println("name=" + wdrug.getName());
			preparedStatement.setString(1, wdrug.getName());
			System.out.println("specification=" + wdrug.getSpecification());
			preparedStatement.setString(2, wdrug.getSpecification());
			System.out.println("drugID=" + wdrug.getID());
			preparedStatement.setInt(3, wdrug.getID());

			number = preparedStatement.executeUpdate();

			return number;
		}

		catch (SQLException e) {
			try {
				connection.rollback();
			} catch (Exception ee) {
				System.out.println(ee.getMessage());
			}
			System.out.println(e.getMessage());

			e.printStackTrace();
		}

		finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}

		return number;
	}
	
	public int deleteDrug(int wdrugid) throws SQLException {
		PreparedStatement preparedStatement = null;

		int number = 0;

		String query = "DELETE FROM drug WHERE drugID=?";
		System.out.println(query);
		try {
			preparedStatement = connection.prepareStatement(query);

			System.out.println("drugID=" + wdrugid);
			preparedStatement.setInt(1, wdrugid);

			number = preparedStatement.executeUpdate();

			return number;
		}

		catch (SQLException e) {
			try {
				connection.rollback();
			} catch (Exception ee) {
				System.out.println(ee.getMessage());
			}
			System.out.println(e.getMessage());

			e.printStackTrace();
		}

		finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}

		return number;
	}
	
	public String getDoctorNameRecords(int whospitalid) {	
		Employee employee = null;
		String doctorNameList = "";

		Statement sment = null;
		ResultSet rs = null;
		
		try {
			sment = connection.createStatement();
			String sql = "SELECT e.EmployeeID, e.firstName, e.lastName "
					+ " FROM schedule s, Employee e WHERE s.hospitalID=" + whospitalid + " and s.doctorID = e.EmployeeID "
					+ "GROUP BY e.EmployeeID, e.firstName, e.lastName";
			System.out.println(sql);

			employee = new Employee();

			rs = sment.executeQuery(sql);

			while (rs.next()) {

				employee.setID(rs.getString("EmployeeID"));
				employee.setFirstName(rs.getString("firstName"));
				employee.setLastName(rs.getString("lastName"));

				doctorNameList += creatXmlTextDoctorName(employee);
			}

			doctorNameList = "<doctornamequery>" + doctorNameList
					+ "</doctornamequery>";
			System.out.println(doctorNameList);
			rs.close();
			sment.close();

		}

		catch (SQLException e) {
			e.printStackTrace();
			doctorNameList = "";
		}

		finally {
			try {
				rs.close();
				sment.close();
			}

			catch (SQLException e) {
				e.printStackTrace();
			}
		}

		// System.out.println("doctorNameList="+doctorNameList);

		return doctorNameList;
	}

	public String creatXmlTextDoctorName(Employee wemployee) {
		String xmltext = "";

		xmltext = "<RD>" + "<id>" + wemployee.getID() + "</id>" + "<name>"
				+ wemployee.getFirstName() + " " +  wemployee.getLastName() + "</name>" + "</RD>";
		System.out.println("xmltext=" + xmltext);

		return xmltext;
	}
	
	public String getScheduleRecords(int whospitalid, String wstartdate, String wenddate, String wdoctorid, String wdoctorname) {	
		
		int  scheduleID, hospitalID, availableTM, isAvailable;
		String workdate, EmployeeID, firstName, lastName, hospitalname; 
		
		String scheduleList = "";
		
		Statement sment = null;
		ResultSet rs = null;
		
		DateFormat dateFormat = new SimpleDateFormat("dd-MMM-yyyy");
		
		try {
			sment = connection.createStatement();
			String sql;
			sql = "SELECT s.scheduleID, s.hospitalID, s.workdate, s.availableTM, h.HospitalName, e.EmployeeID, e.firstName, e.lastName, s.isAvailable  "
					+ "FROM schedule s, Hospital h, Employee e WHERE s.doctorID = e.EmployeeID and s.hospitalID = h.HospitalID ";
			if( whospitalid != 0 )
				sql += " and s.hospitalID = " + whospitalid; 
			if( wstartdate != "" )
				sql += " and workdate >= to_date('" + wstartdate + "', 'dd-mon-yy')";
			if( wenddate != "" )
				sql += " and workdate <= to_date('" + wenddate + "', 'dd-mon-yy')";
			if( wdoctorid != "" )
				sql += " and s.doctorID = '" + wdoctorid +"' ";
			if( wdoctorname != "" && wdoctorname != null )
			{
				sql += " and e.firstName || ' ' || e.lastName like '%" + wdoctorname + "%' ";
			}

			sql += " order by s.workdate ";
			
			System.out.println(sql);

			rs = sment.executeQuery(sql);

			while (rs.next()) {

				scheduleID = rs.getInt("scheduleID");
				workdate = dateFormat.format(rs.getDate("workdate"));
				EmployeeID = rs.getString("EmployeeID");
				firstName = rs.getString("firstName");
				lastName = rs.getString("lastName");
				hospitalID = rs.getInt("hospitalID");
				hospitalname = rs.getString("HospitalName");
				availableTM = rs.getInt("availableTM");
				isAvailable = rs.getInt("isAvailable");
				
				scheduleList += creatXmlTextSchedule(scheduleID, workdate, availableTM, EmployeeID, firstName, lastName, hospitalID, hospitalname, isAvailable);
			}

			scheduleList = "<schedulequery>" + scheduleList
					+ "</schedulequery>";
			System.out.println(scheduleList);
			rs.close();
			sment.close();

		}

		catch (SQLException e) {
			e.printStackTrace();
			scheduleList = "";
		}

		finally {
			try {
				rs.close();
				sment.close();
			}

			catch (SQLException e) {
				e.printStackTrace();
			}
		}

		// System.out.println("scheduleList="+scheduleList);

		return scheduleList;
	}

	public String creatXmlTextSchedule(int wscheduleID, String wworkdate, int wavailableTM, String wEmployeeID, String wfirstName, String wlastName, int whospitalID, String whospitalname, int wisAvailable) {
		String xmltext = "";

		xmltext = "<RD><scheduleid>" + wscheduleID + "</scheduleid><date>"
				+ wworkdate  + "</date><avltm>"
				+ wavailableTM  + "</avltm><employeeid>"
				+ wEmployeeID  + "</employeeid><firstname>"
				+ wfirstName  + "</firstname><lastname>"
				+ wlastName  + "</lastname><hospitalid>"
				+ whospitalID + "</hospitalid><hospitalname>"
				+ whospitalname + "</hospitalname><available>"
				+ wisAvailable  + "</available></RD>";
		System.out.println("xmltext=" + xmltext);

		return xmltext;
	}	
	
	public String getScheduleDetailRecords(int whospitalid, String wdoctorid, String wworkdate) 
	{		
		int counter=0;
		int AMflag=0;
		int PMflag=0;
		int availableTM=9;
		
		ScheduleDetail scheduledetail=null; 
		Schedule schedule = null;
		Employee employee=null;
		TimeSlice timeslice=null;
		
		String doctorname="";
		String patientname="";
		
		String scheduledetailList = "";
		
		Statement sment = null;
		ResultSet rs = null;		
		
		DateFormat dateFormat = new SimpleDateFormat("dd-MMM-yyyy");
		
		try {
			sment = connection.createStatement();
			String sql;
			sql = "SELECT * FROM scheduledetail WHERE hospitalID = " + whospitalid 
					+ " and doctorID = '" + wdoctorid + "' "
					+ " and workdate = to_date('" + wworkdate + "', 'dd-mm-yy') order by hospitalID,doctorID,workdate,sliceid ";
			
			System.out.println(sql);		
			
			rs = sment.executeQuery(sql);
			
			while ( rs.next() ) 
			{		
				counter++;
				scheduledetail = new ScheduleDetail();
				employee = new Employee();
				timeslice = new TimeSlice ();
				doctorname="";
				patientname="";
				
				scheduledetail.setScheduleID(rs.getInt("scheduleID"));
				System.out.println("ScheduleID="+scheduledetail.getScheduleID());
				scheduledetail.setHospitalID(rs.getInt("hospitalID"));
				System.out.println("hospitalID="+scheduledetail.getHospitalID());
				scheduledetail.setWorkdate(dateFormat.format(rs.getDate("workdate")));
				System.out.println("workdate="+scheduledetail.getWorkdate());
				scheduledetail.setSliceid(rs.getInt("sliceid"));
				System.out.println("sliceid="+scheduledetail.getSliceid());
				if( scheduledetail.getSliceid() != 0 )
				{
					Statement subsment = null;
					ResultSet subrs = null;	
					
					try
					{						
						subsment = connection.createStatement();
						sql = "SELECT * FROM timeslice WHERE hospitalID = " +  scheduledetail.getHospitalID() 
								+ " and sliceid = " + scheduledetail.getSliceid();
						subrs = subsment.executeQuery(sql);
						System.out.println(sql);
						while( subrs.next() )
						{
							timeslice.setSliceid(subrs.getInt("sliceid"));
							timeslice.setSlicevalue(subrs.getString("slicevalue"));
							System.out.println("slicevalue="+timeslice.getSlicevalue());
							timeslice.setHalfday(subrs.getInt("halfday"));
							System.out.println("halfday="+timeslice.getHalfday());
						}
					}
					catch (SQLException e) {
						e.printStackTrace();
						scheduledetailList = "";
					}

					finally {
						try {
							subrs.close();
							subsment.close();
						}

						catch (SQLException e) {
							e.printStackTrace();
						}
					}					
					
				}
				
				//get doctor name
				if( rs.getString("doctorID") != "" && rs.getString("doctorID") != null )
				{
					scheduledetail.setDoctorID(rs.getString("doctorID"));
					
					Statement subsment = null;
					ResultSet subrs = null;	
					
					try
					{								
						subsment = connection.createStatement();
						sql = "SELECT * FROM employee WHERE employeeid = '" +  scheduledetail.getDoctorID() + "' ";
						subrs = subsment.executeQuery(sql);
						System.out.println(sql);
						while( subrs.next() )
						{
							employee.setID(subrs.getString("EmployeeID"));
							System.out.println("doctorID="+employee.getID());
							employee.setFirstName(subrs.getString("firstName"));
							System.out.println("firstName="+employee.getFirstName());
							employee.setLastName(subrs.getString("lastName"));	
							System.out.println("lastName="+employee.getLastName());
							doctorname = employee.getFirstName() + " " + employee.getLastName();
						}
					}
					catch (SQLException e) {
						e.printStackTrace();
						scheduledetailList = "";
					}

					finally {
						try {
							subrs.close();
							subsment.close();
						}

						catch (SQLException e) {
							e.printStackTrace();
						}
					}					
				}
				else
					scheduledetail.setDoctorID("");

				//get patient name
				if( rs.getString("patientID") != "" && rs.getString("patientID") != null )
				{				
					scheduledetail.setPatientID(rs.getString("patientID"));
					
					Statement subsment = null;
					ResultSet subrs = null;	
					
					try
					{										
						subsment = connection.createStatement();
						sql = "SELECT * FROM employee WHERE employeeid = '" +  scheduledetail.getPatientID() + "' ";
						subrs = subsment.executeQuery(sql);
						System.out.println(sql);
						while( subrs.next() )
						{
							employee.setID(subrs.getString("EmployeeID"));
							System.out.println("patientID="+employee.getID());
							employee.setFirstName(subrs.getString("firstName"));
							System.out.println("firstName="+employee.getFirstName());
							employee.setLastName(subrs.getString("lastName"));		
							System.out.println("lastName="+employee.getLastName());
							patientname = employee.getFirstName() + " " + employee.getLastName();
						}
					}
					catch (SQLException e) {
						e.printStackTrace();
						scheduledetailList = "";
					}

					finally {
						try {
							subrs.close();
							subsment.close();
						}

						catch (SQLException e) {
							e.printStackTrace();
						}
					}					
				}
				else
					scheduledetail.setPatientID("");
				
				scheduledetail.setIsAvailable(rs.getInt("isAvailable"));

				if( scheduledetail.getIsAvailable() == 1 )
				{
					if( timeslice.getHalfday() == 0 )	//A.M. && available
						AMflag = 1;
					else if( timeslice.getHalfday() == 1)	//P.M. && available
						PMflag = 1;
				}
				
				scheduledetailList += creatXmlTextScheduleDetail(scheduledetail, timeslice, doctorname, patientname);
			}

			scheduledetailList = "<schedulequery>" + scheduledetailList
					+ "</schedulequery>";
			System.out.println(scheduledetailList);
			rs.close();
			sment.close();
		}

		catch (SQLException e) {
			e.printStackTrace();
			scheduledetailList = "";
		}

		finally {
			try {
				rs.close();
				sment.close();
			}

			catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if( counter!=0 )
		{
			// System.out.println("scheduleList="+scheduleList);
			if( AMflag == 1 && PMflag == 0)
				availableTM = 0;		//A.M. available
			else if( AMflag == 0 && PMflag == 1)
				availableTM = 1;		//P.M. available
			else if( AMflag == 1 && PMflag == 1)
				availableTM = 2;		//A.M. & P.M. available
			else 
				availableTM = 9;		//not available
			
			//update schedule table 
			schedule = new Schedule();
			schedule.setHospitalID(scheduledetail.getHospitalID());
			schedule.setDoctorID(scheduledetail.getDoctorID());
			schedule.setWorkdate(scheduledetail.getWorkdate());
			schedule.setAvailableTM(availableTM);
			
			try 
			{
				updateSchedule(schedule);
			} 
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return scheduledetailList;
	}

	public String creatXmlTextScheduleDetail(ScheduleDetail wscheduledetail, TimeSlice wtimeslice, String wdoctorname, String wpatientname) 
	{
		String xmltext = "";

		xmltext = "<RD><scheduleid>" + wscheduledetail.getScheduleID() + "</scheduleid><hospitalid>"
				+ wscheduledetail.getHospitalID() + "</hospitalid><date>"
				+ wscheduledetail.getWorkdate()  + "</date><sliceid>"
				+ wscheduledetail.getSliceid()  + "</sliceid><slicevalue>"
				+ wtimeslice.getSlicevalue() + "</slicevalue><halfday>"
				+ wtimeslice.getHalfday() + "</halfday><doctorID>"
				+ wscheduledetail.getDoctorID()  + "</doctorID><doctorname>"
				+ wdoctorname + "</doctorname><patientID>"
				+ wscheduledetail.getPatientID() + "</patientID><patientname>"
				+ wpatientname + "</patientname><isAvailable>"
				+ wscheduledetail.getIsAvailable()  + "</isAvailable></RD>";
		System.out.println("xmltext=" + xmltext);

		return xmltext;
	}	
	
	public int updateScheduleDetail(ScheduleDetail wscheduledetail) throws SQLException {
		PreparedStatement preparedStatement = null;

		int number = 0;

		String query = "UPDATE scheduledetail SET patientID=?, isAvailable=? WHERE scheduleID=?";
		System.out.println(query);
		try {
			preparedStatement = connection.prepareStatement(query);

			System.out.println("patientid=" + wscheduledetail.getPatientID());
			preparedStatement.setString(1, wscheduledetail.getPatientID());
			System.out.println("isAvailable=" + wscheduledetail.getIsAvailable());
			preparedStatement.setInt(2, wscheduledetail.getIsAvailable());			
			System.out.println("scheduleID=" + wscheduledetail.getScheduleID());
			preparedStatement.setInt(3, wscheduledetail.getScheduleID());

			number = preparedStatement.executeUpdate();

			return number;
		}

		catch (SQLException e) {
			try {
				connection.rollback();
			} catch (Exception ee) {
				System.out.println(ee.getMessage());
			}
			System.out.println(e.getMessage());

			e.printStackTrace();
		}

		finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}

		return number;
	}	
	
	public int updateSchedule(Schedule wschedule) throws SQLException 
	{
		PreparedStatement preparedStatement = null;
		
		String query;
		
		int number = 0;
		
		if( wschedule.getAvailableTM() == 9 )
			query = "UPDATE schedule SET availableTM = ?, isAvailable=0  WHERE hospitalID = ? and doctorID = ? and workdate = to_date(?,'dd-mon-yy') ";
		else
			query = "UPDATE schedule SET availableTM = ?, isAvailable=1  WHERE hospitalID = ? and doctorID = ? and workdate = to_date(?,'dd-mon-yy') ";
		
		System.out.println(query);
		
		try {
			preparedStatement = connection.prepareStatement(query);

			System.out.println("availableTM=" + wschedule.getAvailableTM());
			preparedStatement.setInt(1, wschedule.getAvailableTM());
			System.out.println("hospitalID=" + wschedule.getHospitalID());
			preparedStatement.setInt(2, wschedule.getHospitalID());
			System.out.println("doctorID=" + wschedule.getDoctorID());
			preparedStatement.setString(3, wschedule.getDoctorID());	
			System.out.println("workdate=" + wschedule.getWorkdate());
			preparedStatement.setString(4, wschedule.getWorkdate());	

			number = preparedStatement.executeUpdate();

			return number;
		}

		catch (SQLException e) {
			try {
				connection.rollback();
			} catch (Exception ee) {
				System.out.println(ee.getMessage());
			}
			System.out.println(e.getMessage());

			e.printStackTrace();
		}

		finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}

		return number;
	}
	
	public String getUserListRecords(int roleid) {
		Employee employee = null;
		String employeeList = "";

		Statement sment = null;
		ResultSet rs = null;

		try {
			sment = connection.createStatement();
			String sql = "SELECT * FROM Employee WHERE RoleID = " + roleid + " ORDER BY EmployeeID";
			System.out.println(sql);

			employee = new Employee();
			
			DateFormat dateFormat = new SimpleDateFormat("dd-mmm-yyyy");
			
			rs = sment.executeQuery(sql);

			while (rs.next()) {

				employee.setID(rs.getString("EmployeeID"));
				employee.setLastName(rs.getString("lastName"));
				employee.setFirstName(rs.getString("firstName"));
				employee.setBirthday(dateFormat.format(rs.getDate("birthday")));
				employee.setSex(rs.getString("Sex"));
				employee.setPhone(rs.getString("phone"));
				employee.setEmail(rs.getString("email"));
				employee.setAddress(rs.getString("address"));
				employee.setHospitalid(rs.getInt("hospitalid"));
				employee.setRoleID(rs.getInt("RoleID"));

				employeeList += creatXmlTextEmployees(employee);
			}

			employeeList = "<userlistquery>" + employeeList
					+ "</userlistquery>";

			rs.close();
			sment.close();

		}

		catch (SQLException e) {
			e.printStackTrace();
			employeeList = "";
		}

		finally {
			try {
				rs.close();
				sment.close();
			}

			catch (SQLException e) {
				e.printStackTrace();
			}
		}

		// System.out.println("hospitalList="+hospitalList);

		return employeeList;
	}

	public String creatXmlTextEmployees(Employee wemployee) {
		String xmltext = "";

		xmltext = "<RD>" + "<id>" + wemployee.getID() + "</id>" + "<name>"
				+ wemployee.getFirstName() + " " + wemployee.getLastName() + "</name>" + "<hospitalid>"
				+ wemployee.getHospitalid() + "</hospitalid>" + "<roleid>"
				+ wemployee.getRoleID() + "</roleid>" + "</RD>";
		System.out.println("xmltext=" + xmltext);

		return xmltext;
	}	
	
	
}

